﻿IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'UserTorrents') AND type in (N'U'))
	DROP TABLE UserTorrents

IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'UserStorageInfo') AND type in (N'U'))
	DROP TABLE UserStorageInfo

IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'User') AND type in (N'U'))
	DROP TABLE [User]

IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'Modifications') AND type in (N'U'))
	DROP TABLE Modifications

IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'TrackerInformation') AND type in (N'U'))
	DROP TABLE TrackerInformation


GO

CREATE TABLE [User](
	[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [nvarchar](255) NOT NULL,
	[Password] [nvarchar](255) NOT NULL,
	[EMail] [nvarchar](255) NOT NULL,
	[CreationDate] [datetime] NOT NULL)

GO

CREATE TABLE [UserStorageInfo](
	[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[UserId] [bigint] NOT NULL,
	[StorageFolder] [nvarchar](255) NULL,
	[Token] [image] NULL)

GO

CREATE TABLE [Modifications](
	[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Description] [VARCHAR](255) NOT NULL,
	[Regex] [text] NOT NULL,
	[ReplaceWith] [text] NULL) 
GO

CREATE TABLE [UserTorrents](
	[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[UserId] [bigint] NOT NULL,
	[TopicUrl] VARCHAR(1000) NOT NULL,
	[DownloadedDate] [DATETIME] NOT NULL
)

GO

CREATE TABLE TrackerInformation(
	[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] VARCHAR(50) NOT NULL,
	[LoginPageUrl] TEXT NOT NULL,
	[DefaultUrl] TEXT NOT NULL,
	[SearchUrl] TEXT NOT NULL,
	[RootUrl] TEXT NOT NULL,
	[SearchQueryArg] TEXT NOT NULL,
)

ALTER TABLE [UserTorrents]  WITH CHECK ADD  CONSTRAINT [FK_UserTorrents_UserId] FOREIGN KEY([UserId])
REFERENCES [User] ([Id])
GO
ALTER TABLE [UserStorageInfo]  WITH CHECK ADD  CONSTRAINT [FK_User_Id_UserId] FOREIGN KEY([UserId])
REFERENCES [User] ([Id])
GO
ALTER TABLE [UserStorageInfo] CHECK CONSTRAINT [FK_User_Id_UserId]
GO

INSERT INTO Modifications ([Description], Regex) VALUES ('Removing security javascript', 'if\s*\(\s*top\s*!=\s*self\s*\)\s*\{\s*allowed_self\s*=\s*/\^\(\s*translate\\\.googleusercontent\\\.com\s*\)\$/\s*;.*?\n\s*if\s*\(!self\.location\.hostname\.match\(allowed_self\)\)\s*\{\s*\n\s*\$\s*\(\s*function\(\s*\)\s*\{\s*\n.*?\n\s*\}\s*\)\s*;\s*\n\s*\}\s*\n\s*}');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing comments', '\<\!--(.|\n)*?--\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing main pane', '\<div\s*id=\"main-nav\"(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing logo', '\<div\s*id=\"logo\"(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing top menu', '\<div\s*class=\"topmenu\"(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing search options', '\<div\s*id=\"search_opt\"(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing body_container', '\<div\s*id=\"body_container\"(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing page_footer', '\<div\s*id=\"page_footer\"(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing advertise', '\<div\s*id=\"owo-idx-6\"(.|\n)*?\</div\>');
INSERT INTO Modifications ([Description], Regex) VALUES ('Removing iframes', '\<iframe\s*(.|\n)*ss?(\</iframe\>|\</\>)'); 

INSERT INTO TrackerInformation ([Name], [LoginPageUrl], [DefaultUrl], [SearchUrl], [RootUrl], [SearchQueryArg]) VALUES('rutracker.org', 'http://login.rutracker.org/forum/login.php', 'http://rutracker.org/forum/index.php', 'http://rutracker.org/forum/tracker.php', 'http://rutracker.org/forum', 'nm')
